﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using Hangjing.DBUtility;
using Hangjing.Model;

namespace Hangjing.SQLServerDAL
{
    /// <summary>
    /// 出入库订单商品明细
    /// </summary>
    public partial class StorageLog
    {
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(StorageLogInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into StorageLog(");
            strSql.Append("stype,orderid,devid,changestock,remark,adddate,reveint1,reveint2,reveint3,revevar1,revevar2,revevar3,revefloat1");
            strSql.Append(") values (");
            strSql.Append("@stype,@orderid,@devid,@changestock,@remark,@adddate,@reveint1,@reveint2,@reveint3,@revevar1,@revevar2,@revevar3,@revefloat1");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters =
            {
                        new SqlParameter("@stype", SqlDbType.Int,4) ,
                        new SqlParameter("@orderid", SqlDbType.VarChar,50) ,
                        new SqlParameter("@devid", SqlDbType.Int,4) ,
                        new SqlParameter("@changestock", SqlDbType.Int,4) ,
                        new SqlParameter("@remark", SqlDbType.VarChar,256) ,
                        new SqlParameter("@adddate", SqlDbType.DateTime) ,
                        new SqlParameter("@reveint1", SqlDbType.Int,4) ,
                        new SqlParameter("@reveint2", SqlDbType.Int,4) ,
                        new SqlParameter("@reveint3", SqlDbType.Int,4) ,
                        new SqlParameter("@revevar1", SqlDbType.VarChar,256) ,
                        new SqlParameter("@revevar2", SqlDbType.VarChar,256) ,
                        new SqlParameter("@revevar3", SqlDbType.VarChar,256) ,
                        new SqlParameter("@revefloat1", SqlDbType.Float,8)

            };

            parameters[0].Value = model.stype;
            parameters[1].Value = model.orderid;
            parameters[2].Value = model.devid;
            parameters[3].Value = model.changestock;
            parameters[4].Value = model.remark;
            parameters[5].Value = model.adddate;
            parameters[6].Value = model.reveint1;
            parameters[7].Value = model.reveint2;
            parameters[8].Value = model.reveint3;
            parameters[9].Value = model.revevar1;
            parameters[10].Value = model.revevar2;
            parameters[11].Value = model.revevar3;
            parameters[12].Value = model.revefloat1;
            return HJConvert.ToInt32(SQLHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters));
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(StorageLogInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update StorageLog set ");

            strSql.Append(" stype = @stype , ");
            strSql.Append(" orderid = @orderid , ");
            strSql.Append(" devid = @devid , ");
            strSql.Append(" changestock = @changestock , ");
            strSql.Append(" remark = @remark , ");
            strSql.Append(" adddate = @adddate , ");
            strSql.Append(" reveint1 = @reveint1 , ");
            strSql.Append(" reveint2 = @reveint2 , ");
            strSql.Append(" reveint3 = @reveint3 , ");
            strSql.Append(" revevar1 = @revevar1 , ");
            strSql.Append(" revevar2 = @revevar2 , ");
            strSql.Append(" revevar3 = @revevar3 , ");
            strSql.Append(" revefloat1 = @revefloat1  ");
            strSql.Append(" where sId=@sId ");

            SqlParameter[] parameters =
            {
                        new SqlParameter("@sId", SqlDbType.Int,4) ,
                        new SqlParameter("@stype", SqlDbType.Int,4) ,
                        new SqlParameter("@orderid", SqlDbType.VarChar,50) ,
                        new SqlParameter("@devid", SqlDbType.Int,4) ,
                        new SqlParameter("@changestock", SqlDbType.Int,4) ,
                        new SqlParameter("@remark", SqlDbType.VarChar,256) ,
                        new SqlParameter("@adddate", SqlDbType.DateTime) ,
                        new SqlParameter("@reveint1", SqlDbType.Int,4) ,
                        new SqlParameter("@reveint2", SqlDbType.Int,4) ,
                        new SqlParameter("@reveint3", SqlDbType.Int,4) ,
                        new SqlParameter("@revevar1", SqlDbType.VarChar,256) ,
                        new SqlParameter("@revevar2", SqlDbType.VarChar,256) ,
                        new SqlParameter("@revevar3", SqlDbType.VarChar,256) ,
                        new SqlParameter("@revefloat1", SqlDbType.Float,8)

            };

            parameters[0].Value = model.sId;
            parameters[1].Value = model.stype;
            parameters[2].Value = model.orderid;
            parameters[3].Value = model.devid;
            parameters[4].Value = model.changestock;
            parameters[5].Value = model.remark;
            parameters[6].Value = model.adddate;
            parameters[7].Value = model.reveint1;
            parameters[8].Value = model.reveint2;
            parameters[9].Value = model.reveint3;
            parameters[10].Value = model.revevar1;
            parameters[11].Value = model.revevar2;
            parameters[12].Value = model.revevar3;
            parameters[13].Value = model.revefloat1;
            return SQLHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取记录所有字段
        /// </summary>
        /// 此代码由杭景科技代码内部生成器自动生成
        /// <param>sId</param>
        /// <returns>StorageLogInfo</returns>
        public StorageLogInfo GetModel(int sId)
        {
            string sql = "select sId,stype,orderid,devid,changestock,remark,adddate,reveint1,reveint2,reveint3,revevar1,revevar2,revevar3,revefloat1 from StorageLog where  sId = @sId";
            SqlParameter parameter = new SqlParameter("@sId", SqlDbType.Int, 4);
            parameter.Value = sId;
            StorageLogInfo model = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(CommandType.Text, sql, parameter))
            {
                if (dr.Read())
                {
                    model = new StorageLogInfo();
                    model.sId = HJConvert.ToInt32(dr["sId"]);
                    model.stype = HJConvert.ToInt32(dr["stype"]);
                    model.orderid = HJConvert.ToString(dr["orderid"]);
                    model.devid = HJConvert.ToInt32(dr["devid"]);
                    model.changestock = HJConvert.ToInt32(dr["changestock"]);
                    model.remark = HJConvert.ToString(dr["remark"]);
                    model.adddate = HJConvert.ToDateTime(dr["adddate"]);
                    model.reveint1 = HJConvert.ToInt32(dr["reveint1"]);
                    model.reveint2 = HJConvert.ToInt32(dr["reveint2"]);
                    model.reveint3 = HJConvert.ToInt32(dr["reveint3"]);
                    model.revevar1 = HJConvert.ToString(dr["revevar1"]);
                    model.revevar2 = HJConvert.ToString(dr["revevar2"]);
                    model.revevar3 = HJConvert.ToString(dr["revevar3"]);
                    model.revefloat1 = HJConvert.ToDecimal(dr["revefloat1"]);
                }
            }
            return model;
        }

        /// <summary>
        /// 获得总记录数
        /// </summary>
        /// 此代码由杭景科技代码内部生成器自动生成
        public int GetCount(string strWhere)
        {
            return (int)SQLHelper.ExecuteScalar(CommandType.StoredProcedure, "pagecount", new SqlParameter("@tblName", "StorageLog"), new SqlParameter("@strWhere", strWhere));
        }

        /// <summary>
        /// 获得总记录数
        /// </summary>
        /// 此代码由杭景科技代码内部生成器自动生成
        public int GeSpantCount(string strWhere)
        {
            return (int)SQLHelper.ExecuteScalar(CommandType.StoredProcedure, "pagecount", new SqlParameter("@tblName", "StorageLog left join StorageOrder on StorageLog.reveint1 = StorageOrder.id"), new SqlParameter("@strWhere", strWhere));
        }

        /// <summary>
        /// 物料分布
        /// </summary>
        /// <param name="pagesize">页尺寸</param>
        /// <param name="pageindex">页索引</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="orderName">排序字段</param>
        /// <param name="orderType">排序类型，1为降序，0为升序</param>
        /// <returns>列表</returns>
        ///此代码由杭景科技代码内部生成器自动生成
        public IList<StorageLogChildInfo> GetSpanList(int pagesize, int pageindex, string strWhere, string orderName, int orderType)
        {
            IList<CityInfo> citys = CacheHelper.GetCityList();
            IList<DeliverGroupInfo> DeliverGroup = CacheHelper.GetEdelivergroupList();

            IList<StorageLogChildInfo> infos = new List<StorageLogChildInfo>();
            SqlParameter[] parameters =
            {
                new SqlParameter("@tblName", SqlDbType.VarChar,255),
                new SqlParameter("@strGetFields", SqlDbType.VarChar,1000),
                new SqlParameter("@primary", SqlDbType.VarChar,255),
                new SqlParameter("@orderName", SqlDbType.VarChar,255),
                new SqlParameter("@PageSize", SqlDbType.Int),
                new SqlParameter("@PageIndex", SqlDbType.Int),
                new SqlParameter("@OrderType", SqlDbType.Bit),
                new SqlParameter("@strWhere", SqlDbType.VarChar,1500)
            };
            parameters[0].Value = " StorageLog left join StorageOrder on StorageLog.reveint1 = StorageOrder.id ";
            parameters[1].Value = "StorageLog.*,StorageOrder.cityid,StorageOrder.gid,adminname";
            parameters[2].Value = "sId";
            parameters[3].Value = orderName;
            parameters[4].Value = pagesize;
            parameters[5].Value = pageindex;
            parameters[6].Value = orderType;
            parameters[7].Value = strWhere;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(CommandType.StoredProcedure, "pageselectprifix", parameters))
            {
                while (dr.Read())
                {
                    StorageLogChildInfo model = new StorageLogChildInfo();
                    model.sId = HJConvert.ToInt32(dr["sId"]);
                    model.stype = HJConvert.ToInt32(dr["stype"]);
                    model.orderid = HJConvert.ToString(dr["orderid"]);
                    model.devid = HJConvert.ToInt32(dr["devid"]);
                    model.changestock = HJConvert.ToInt32(dr["changestock"]);
                    model.remark = HJConvert.ToString(dr["remark"]);
                    model.adddate = HJConvert.ToDateTime(dr["adddate"]);
                    model.reveint1 = HJConvert.ToInt32(dr["reveint1"]);
                    model.reveint2 = HJConvert.ToInt32(dr["reveint2"]);
                    model.reveint3 = HJConvert.ToInt32(dr["reveint3"]);
                    model.revevar1 = HJConvert.ToString(dr["revevar1"]);
                    model.revevar2 = HJConvert.ToString(dr["revevar2"]);
                    model.revevar3 = HJConvert.ToString(dr["revevar3"]);
                    model.revefloat1 = HJConvert.ToDecimal(dr["revefloat1"]);
                    model.adminname = HJConvert.ToString(dr["adminname"]);

                    
                    int cityid = HJConvert.ToInt32(dr["cityid"]);
                    model.cityname = "";
                    foreach (var item in citys)
                    {
                        if (item.cid == cityid)
                        {
                            model.cityname = item.cname;
                            break;
                        }

                    }

                    int gid = HJConvert.ToInt32(dr["gid"]);
                    model.gname = "";
                    foreach (var item in DeliverGroup)
                    {
                        if (item.ID == gid)
                        {
                            model.gname = item.classname;
                            break;
                        }

                    }

                    infos.Add(model);
                }
            }
            return infos;
        }


        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="pagesize">页尺寸</param>
        /// <param name="pageindex">页索引</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="orderName">排序字段</param>
        /// <param name="orderType">排序类型，1为降序，0为升序</param>
        /// <returns>列表</returns>
        ///此代码由杭景科技代码内部生成器自动生成
        public IList<StorageLogInfo> GetList(int pagesize, int pageindex, string strWhere, string orderName, int orderType)
        {
            IList<StorageLogInfo> infos = new List<StorageLogInfo>();
            SqlParameter[] parameters =
                    {
                new SqlParameter("@tblName", SqlDbType.VarChar,255),
                new SqlParameter("@strGetFields", SqlDbType.VarChar,1000),
                new SqlParameter("@primary", SqlDbType.VarChar,255),
                new SqlParameter("@orderName", SqlDbType.VarChar,255),
                new SqlParameter("@PageSize", SqlDbType.Int),
                new SqlParameter("@PageIndex", SqlDbType.Int),
                new SqlParameter("@OrderType", SqlDbType.Bit),
                new SqlParameter("@strWhere", SqlDbType.VarChar,1500)
            };
            parameters[0].Value = "StorageLog";
            parameters[1].Value = "sId,stype,orderid,devid,changestock,remark,adddate,reveint1,reveint2,reveint3,revevar1,revevar2,revevar3,revefloat1";
            parameters[2].Value = "sId";
            parameters[3].Value = orderName;
            parameters[4].Value = pagesize;
            parameters[5].Value = pageindex;
            parameters[6].Value = orderType;
            parameters[7].Value = strWhere;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(CommandType.StoredProcedure, "pageselectpri", parameters))
            {
                while (dr.Read())
                {
                    StorageLogInfo model = new StorageLogInfo();
                    model.sId = HJConvert.ToInt32(dr["sId"]);
                    model.stype = HJConvert.ToInt32(dr["stype"]);
                    model.orderid = HJConvert.ToString(dr["orderid"]);
                    model.devid = HJConvert.ToInt32(dr["devid"]);
                    model.changestock = HJConvert.ToInt32(dr["changestock"]);
                    model.remark = HJConvert.ToString(dr["remark"]);
                    model.adddate = HJConvert.ToDateTime(dr["adddate"]);
                    model.reveint1 = HJConvert.ToInt32(dr["reveint1"]);
                    model.reveint2 = HJConvert.ToInt32(dr["reveint2"]);
                    model.reveint3 = HJConvert.ToInt32(dr["reveint3"]);
                    model.revevar1 = HJConvert.ToString(dr["revevar1"]);
                    model.revevar2 = HJConvert.ToString(dr["revevar2"]);
                    model.revevar3 = HJConvert.ToString(dr["revevar3"]);
                    model.revefloat1 = HJConvert.ToDecimal(dr["revefloat1"]);
                    infos.Add(model);
                }
            }
            return infos;
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <param name="Id"></param>
        /// 此代码由杭景科技代码内部生成器自动生成
        /// <returns></returns>
        public int DelStorageLog(int Id)
        {
            StringBuilder str = new StringBuilder();
            str.Append("delete from StorageLog where sId=@sId");
            SqlParameter[] Para =
                {
                new SqlParameter("@sId",SqlDbType.Int)
            };
            Para[0].Value = Id;

            return SQLHelper.ExecuteNonQuery(CommandType.Text, str.ToString(), Para);
        }


        /// <summary>
        /// 批量删除记录
        /// </summary>
        /// <param name="IdList"></param>
        /// <returns></returns>
        /// 此代码由杭景科技代码内部生成器自动生成
        public int DelList(string IdList)
        {
            StringBuilder str = new StringBuilder();
            str.Append("delete from StorageLog where sId in ({0})");

            return SQLHelper.ExecuteNonQuery(CommandType.Text, string.Format(str.ToString(), IdList), null);
        }



    }
}

